﻿using Infrastructures;
using IRepository;
using Models;
using Spring.Data.Common;
using Spring.Data.Generic;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RepositoryMySQL
{
    public class ProjectRepository : IProjectRepository
    {
        #region Project

        private string baseProject = "select ProjectID,ProjectName,Address,Amount,StartDate,EndDate,Status,Reamrk,CreatorID,CreatorType,CreatorName,CreateDate,UpdateDate,UpdateUser,Cover,CreatorBelongID from Project";
        public Project Create(Project obj)
        {
            string strIns = @"insert into Project(ProjectName,Address,Amount,StartDate,EndDate,Status,Reamrk,CreatorID,CreatorType,CreatorName,CreateDate,UpdateDate,UpdateUser,Cover,CreatorBelongID) values(@ProjectName,@Address,@Amount,@StartDate,@EndDate,@Status,@Reamrk,@CreatorID,@CreatorType,@CreatorName,@CreateDate,@UpdateDate,@UpdateUser,@Cover,@CreatorBelongID);SELECT LAST_INSERT_ID() as id";
            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            //p.Add("ProjectID", DbType.Int32).Value = obj.ProjectID;
            p.Add("ProjectName", DbType.String).Value = obj.ProjectName;
            p.Add("Address", DbType.String).Value = obj.Address;
            p.Add("Amount", DbType.Double).Value = obj.Amount;
            p.Add("StartDate", DbType.DateTime).Value = obj.StartDate;
            p.Add("EndDate", DbType.DateTime).Value = obj.EndDate;
            p.Add("Status", DbType.String).Value = obj.Status;
            p.Add("Reamrk", DbType.String).Value = obj.Reamrk;
            p.Add("CreatorID", DbType.Int32).Value = obj.CreatorID;
            p.Add("CreatorType", DbType.String).Value = obj.CreatorType;
            p.Add("CreatorName", DbType.String).Value = obj.CreatorName;
            p.Add("CreateDate", DbType.DateTime).Value = obj.CreateDate;
            p.Add("UpdateDate", DbType.DateTime).Value = obj.UpdateDate;
            p.Add("UpdateUser", DbType.Int32).Value = obj.UpdateUser;
            p.Add("Cover", DbType.String).Value = obj.Cover;
            p.Add("CreatorBelongID", DbType.Int32).Value = obj.CreatorBelongID;

            int num = Int32.Parse(DBHelper.TemplateMySQL.ExecuteScalar(CommandType.Text, strIns, p).ToString());
            obj.ProjectID = num;
            return obj;
        }
        public void Update(Project obj)
        {
            string strUpdate = @"update Project set ProjectName=@ProjectName,Address=@Address,Amount=@Amount,StartDate=@StartDate,EndDate=@EndDate,Status=@Status,Reamrk=@Reamrk,UpdateDate=@UpdateDate,UpdateUser=@UpdateUser,Cover=@Cover where ProjectID=@ProjectID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            p.Add("ProjectID", DbType.Int32).Value = obj.ProjectID;
            p.Add("ProjectName", DbType.String).Value = obj.ProjectName;
            p.Add("Address", DbType.String).Value = obj.Address;
            p.Add("Amount", DbType.Double).Value = obj.Amount;
            p.Add("StartDate", DbType.DateTime).Value = obj.StartDate;
            p.Add("EndDate", DbType.DateTime).Value = obj.EndDate;
            p.Add("Status", DbType.String).Value = obj.Status;
            p.Add("Reamrk", DbType.String).Value = obj.Reamrk;
            p.Add("UpdateDate", DbType.DateTime).Value = obj.UpdateDate;
            p.Add("UpdateUser", DbType.Int32).Value = obj.UpdateUser;
            p.Add("Cover", DbType.String).Value = obj.Cover;

            DBHelper.TemplateMySQL.ExecuteNonQuery(CommandType.Text, strUpdate, p);
        }

        public Project Get(string projectid)
        {
            string strSel = baseProject + " where ProjectID=@ProjectID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("ProjectID", DbType.Int32).Value = projectid.ToInt();


            IList<Project> lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<Project>(MapProject), p);
            return lst.Count == 0 ? null : lst[0];
        }
        public IList<Project> List()
        {
            IList<Project> lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, baseProject, new RowMapperDelegate<Project>(MapProject));

            return lst;
        }

        public IList<Project> ListProjectByRole(APPRole createType, int topNumber)
        {
            string strSel = baseProject + " where CreatorType=@CreatorType and Status=@ProjectStatus order by CreateDate desc limit @topNumber";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("CreatorType", DbType.String).Value = createType.ToString();
            p.Add("topNumber", DbType.Int32).Value = topNumber == 0 ? 12 : topNumber;
            p.Add("ProjectStatus", DbType.String).Value = ProjectStatus.Open.ToString();

            IList<Project> lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<Project>(MapProject), p);

            return lst;
        }

        public ModelList<Project> Search(IDictionary<SearchKey, string> dicCondition, int start, int limit)
        {
            StringBuilder sb = new StringBuilder();
            string strCondition = string.Empty;
            start = start == 0 ? 1 : start;
            limit = limit == 0 ? 10 : limit;

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("start", DbType.Int32).Value = (start - 1) * limit;
            p.Add("limit", DbType.Int32).Value = limit;

            foreach (var item in dicCondition)
            {
                switch (item.Key)
                {
                    case SearchKey.StartDate:
                        if (!item.Value.IsNullOrEmpty())
                        {
                            strCondition += " and StartDate >= @StartDate";
                            p.Add("StartDate", DbType.String).Value = item.Value;
                        }
                        break;
                    case SearchKey.EndDate:
                        if (!item.Value.IsNullOrEmpty())
                        {
                            strCondition += " and EndDate <= @EndDate";
                            p.Add("EndDate", DbType.String).Value = item.Value;
                        }
                        break;
                    case SearchKey.SearchFrom:
                        if (!item.Value.IsNullOrEmpty())
                        {
                            strCondition += " and CreatorType = @CreatorType";
                            p.Add("CreatorType", DbType.String).Value = item.Value;
                        }
                        break;
                    case SearchKey.ProjectStatus:

                        if (!item.Value.IsNullOrEmpty())
                        {
                            strCondition += " and Status = @Status";
                            p.Add("Status", DbType.String).Value = item.Value;
                        }

                        break;
                    case SearchKey.CreatorID:

                        if (!item.Value.IsNullOrEmpty())
                        {
                            strCondition += " and CreatorID = @CreatorID";
                            p.Add("CreatorID", DbType.String).Value = item.Value;
                        }

                        break;
                    case SearchKey.SchoolName:
                        if (!item.Value.IsNullOrEmpty())
                            sb.Append("+" + item.Value + "* ");
                        break;
                    default:
                        if (!item.Value.IsNullOrEmpty())
                            sb.Append("+" + item.Value + " ");
                        break;
                }
            }

            string strSel = string.Empty;
            if (sb.Length == 0)
                strSel = "select sql_calc_found_rows ProjectID,ProjectName,Address,Amount,StartDate,EndDate,Status,Reamrk,CreatorID,CreatorType,CreatorName,CreateDate,UpdateDate,UpdateUser,Cover,CreatorBelongID from Project where 1=1"
                    + strCondition + " limit @start,@limit;select found_rows()";
            else
            {
                p.Add("condition", DbType.String).Value = sb.ToString();

                strSel = "select sql_calc_found_rows ProjectID,ProjectName,Address,Amount,StartDate,EndDate,Status,Reamrk,CreatorID,CreatorType,CreatorName,CreateDate,UpdateDate,UpdateUser,Cover,CreatorBelongID from Project"
                    + " where ProjectID in"
                    + " (select projectid FROM projectsearch"
                    + " WHERE MATCH (keywords) AGAINST (@condition IN BOOLEAN MODE)) "
                    + strCondition + " limit @start,@limit;select found_rows()";
            }

            ModelList<Project> lst = new ModelList<Project>();

            DBHelper.TemplateMySQL.QueryWithRowCallbackDelegate(CommandType.Text, strSel, (dr) =>
            {
                lst.Add(MapProject(dr, 0));

                while (dr.Read())
                    lst.Add(MapProject(dr, 0));

                bool isNext = dr.NextResult();

                while (dr.Read() && isNext)
                    lst.TotalCount = dr.GetInt32(0);

            }, p);

            return lst;
        }

        public ModelList<Project> ListProjectByBelongID(int belongID, int start, int limit)
        {
            string strSel = "select sql_calc_found_rows ProjectID,ProjectName,Address,Amount,StartDate,EndDate,Status,Reamrk,CreatorID,CreatorType,CreatorName,CreateDate,UpdateDate,UpdateUser,Cover,CreatorBelongID from Project "
                + " where CreatorBelongID=@CreatorBelongID and Status='Open' limit @start,@limit;select found_rows()";

            start = start == 0 ? 1 : start;
            limit = limit == 0 ? 10 : limit;

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("start", DbType.Int32).Value = (start - 1) * limit;
            p.Add("limit", DbType.Int32).Value = limit;
            p.Add("CreatorBelongID", DbType.Int32).Value = belongID;

            ModelList<Project> lst = new ModelList<Project>();

            DBHelper.TemplateMySQL.QueryWithRowCallbackDelegate(CommandType.Text, strSel, (dr) =>
            {
                lst.Add(MapProject(dr, 0));

                while (dr.Read())
                    lst.Add(MapProject(dr, 0));

                bool isNext = dr.NextResult();

                while (dr.Read() && isNext)
                    lst.TotalCount = dr.GetInt32(0);

            }, p);

            return lst;
        }

        public int CountProjectCreatedPerDay(int userID)
        {
            string strsql = "select count(1) from project where CreatorID=@CreatorID and CreateDate > date(now())";
            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("CreatorID", DbType.Int32).Value = userID;

            object obj = DBHelper.TemplateMySQL.ExecuteScalar(CommandType.Text, strsql, p);
            return obj == null ? 0 : obj.ToString().ToInt();
        }

        #endregion

        #region ProjectContract
        private string baseProjectContract = "select ContractID,ProjectID,AgentID,ContractCode,Status,Amount,ContractorID,ContractorName,CreateDate,SignDate,UpdateDate,UpdateUser,SubStatus,TemplateVersion from ProjectContract";

        public ProjectContract CreateProjectContract(ProjectContract obj)
        {
            string strIns = @"insert into ProjectContract(ProjectID,AgentID,ContractCode,Status,Amount,ContractorID,ContractorName,CreateDate,SignDate,UpdateDate,UpdateUser,SubStatus,TemplateVersion) values(@ProjectID,@AgentID,@ContractCode,@Status,@Amount,@ContractorID,@ContractorName,@CreateDate,@SignDate,@UpdateDate,@UpdateUser,@SubStatus,@TemplateVersion);SELECT LAST_INSERT_ID() as id";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            //p.Add("ContractID", DbType.Int32).Value = obj.ContractID;
            p.Add("ProjectID", DbType.Int32).Value = obj.ProjectID;
            p.Add("AgentID", DbType.Int32).Value = obj.AgentID;
            p.Add("ContractCode", DbType.String).Value = obj.ContractCode;
            p.Add("TemplateVersion", DbType.String).Value = obj.TemplateVersion;
            p.Add("Status", DbType.String).Value = obj.Status;
            p.Add("SubStatus", DbType.String).Value = CommonStatus.Pending;
            p.Add("Amount", DbType.Double).Value = obj.Amount;
            p.Add("ContractorID", DbType.Int32).Value = obj.ContractorID;
            p.Add("ContractorName", DbType.String).Value = obj.ContractorName;
            p.Add("CreateDate", DbType.DateTime).Value = obj.CreateDate;
            p.Add("SignDate", DbType.DateTime).Value = obj.SignDate;
            p.Add("UpdateDate", DbType.DateTime).Value = obj.UpdateDate;
            p.Add("UpdateUser", DbType.Int32).Value = obj.UpdateUser;

            int num = Int32.Parse(DBHelper.TemplateMySQL.ExecuteScalar(CommandType.Text, strIns, p).ToString());
            obj.ContractID = num;

            //DBHelper.TemplateMySQL.ExecuteNonQuery(CommandType.Text, strIns, p);
            return obj;
        }

        public void UpdateProjectContract(ProjectContract obj)
        {
            string strUpdate = @"update ProjectContract set AgentID=@AgentID,ContractCode=@ContractCode,TemplateVersion=@TemplateVersion,Status=@Status,SubStatus=@SubStatus,Amount=@Amount,ContractorName=@ContractorName,SignDate=@SignDate,UpdateDate=@UpdateDate,UpdateUser=@UpdateUser where ContractID=@ContractID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            p.Add("ContractID", DbType.Int32).Value = obj.ContractID;
            p.Add("ProjectID", DbType.Int32).Value = obj.ProjectID;
            p.Add("AgentID", DbType.Int32).Value = obj.AgentID;
            p.Add("ContractCode", DbType.String).Value = obj.ContractCode;
            p.Add("TemplateVersion", DbType.String).Value = obj.TemplateVersion;
            p.Add("Status", DbType.String).Value = obj.Status;
            p.Add("SubStatus", DbType.String).Value = obj.SubStatus;
            p.Add("Amount", DbType.Double).Value = obj.Amount;
            p.Add("ContractorID", DbType.Int32).Value = obj.ContractorID;
            p.Add("ContractorName", DbType.String).Value = obj.ContractorName;
            p.Add("CreateDate", DbType.DateTime).Value = obj.CreateDate;
            p.Add("SignDate", DbType.DateTime).Value = obj.SignDate;
            p.Add("UpdateDate", DbType.DateTime).Value = obj.UpdateDate;
            p.Add("UpdateUser", DbType.Int32).Value = obj.UpdateUser;

            DBHelper.TemplateMySQL.ExecuteNonQuery(CommandType.Text, strUpdate, p);
        }

        public void UpdateContractStatusByProject(int projectID, CommonStatus status)
        {
            string strUpdate = "update ProjectContract set Status=@Status where ProjectID=@ProjectID";
            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("ProjectID", DbType.Int32).Value = projectID;
            p.Add("Status", DbType.String).Value = status.ToString();

            DBHelper.TemplateMySQL.ExecuteNonQuery(CommandType.Text, strUpdate, p);
        }

        public ProjectContract GetProjectContract(int contractid)
        {
            string strSel = baseProjectContract + " where ContractID=@ContractID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("ContractID", DbType.Int32).Value = contractid;


            IList<ProjectContract> lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<ProjectContract>(MapProjectContract), p);
            return lst.Count == 0 ? null : lst[0];
        }

        public ProjectContract GetProjectContract(int projectID, int contractorID)
        {
            string strSel = baseProjectContract + " where projectID=@projectID and ContractorID=@ContractorID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("projectID", DbType.Int32).Value = projectID;
            p.Add("ContractorID", DbType.Int32).Value = contractorID;

            IList<ProjectContract> lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<ProjectContract>(MapProjectContract), p);
            return lst.Count == 0 ? null : lst[0];
        }

        public IList<ProjectContract> ListProjectContract(int projectID, CommonStatus status)
        {
            string strSel = string.Empty;

            if (status == CommonStatus.All)
                strSel = baseProjectContract + " where ProjectID=@ProjectID";
            else
                strSel = baseProjectContract + " where ProjectID=@ProjectID and Status=@Status";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("ProjectID", DbType.Int32).Value = projectID;
            p.Add("Status", DbType.String).Value = status.ToString();

            return DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<ProjectContract>(MapProjectContract), p);
        }

        public ModelList<IDictionary<string, string>> ListContractByStatus(int creatorID, CommonStatus status, int start, int limit)
        {
            string strCondition = status == CommonStatus.Effective ? "a.Status=@ProjectStatus and" : string.Empty;
            start = start == 0 ? 1 : start;
            limit = limit == 0 ? 10 : limit;

            string strSel = "select sql_calc_found_rows a.ProjectName, b.ContractID, b.SignDate, b.Amount, b.ContractorName, a.Amount, b.CreateDate, b.Status, a.ProjectID, a.CreatorName, a.CreatorID from Project a join ProjectContract b on a.ProjectID=b.ProjectID "
             + " where " + strCondition + " a.CreatorID=@CreatorID and b.Status=@ContractStatus or b.ContractorID=@CreatorID and b.status=@ContractStatus limit @start,@limit;select found_rows()";

            return ListContractByStatus(creatorID, strSel, status.ToString(), start, limit);
        }

        public ModelList<IDictionary<string, string>> ListContractByStatus(int creatorID, IList<CommonStatus> status, int start, int limit)
        {
            start = start == 0 ? 1 : start;
            limit = limit == 0 ? 10 : limit;

            string strSel = "select sql_calc_found_rows a.ProjectName, b.ContractID, b.SignDate, b.Amount, b.ContractorName, a.Amount, b.CreateDate, b.Status, a.ProjectID, a.CreatorName, a.CreatorID from Project a join ProjectContract b on a.ProjectID=b.ProjectID "
             + " where a.CreatorID=@CreatorID and b.Status in (" + status.ListToInClause() + ") or b.ContractorID=@CreatorID and b.status in (" + status.ListToInClause() + ") limit @start,@limit;select found_rows()";

            return ListContractByStatus(creatorID, strSel, status.ListToInClause(), start, limit);
        }

        public ModelList<IDictionary<string, string>> ListSponsorContract(int contractorID, IList<CommonStatus> status, int start, int limit)
        {
            start = start == 0 ? 1 : start;
            limit = limit == 0 ? 10 : limit;

            string strSel = "select sql_calc_found_rows a.ProjectName, b.ContractID, b.SignDate, b.Amount, b.ContractorName, a.Amount, b.CreateDate, b.Status, a.ProjectID, a.CreatorName, a.CreatorID from Project a join ProjectContract b on a.ProjectID=b.ProjectID "
             + " where b.ContractorID=@CreatorID and b.Status in (" + status.ListToInClause() + ") limit @start,@limit;select found_rows()";

            return ListContractByStatus(contractorID, strSel, status.ListToInClause(), start, limit);
        }

        public ModelList<IDictionary<string, string>> ListContractByAgent(int agentID, IList<CommonStatus> status, int start, int limit)
        {
            start = start == 0 ? 1 : start;
            limit = limit == 0 ? 10 : limit;

            string strSel = "select sql_calc_found_rows a.ProjectName, b.ContractID, b.SignDate, b.Amount, b.ContractorName, a.Amount, b.CreateDate, b.Status, a.ProjectID, a.CreatorName, a.CreatorID from Project a join ProjectContract b on a.ProjectID=b.ProjectID "
             + " where b.AgentID=@CreatorID and b.Status in (" + status.ListToInClause() + ") limit @start,@limit;select found_rows()";

            return ListContractByStatus(agentID, strSel, status.ListToInClause(), start, limit);
        }

        public IList<IDictionary<string, string>> ListSponsorMessage()
        {
            string strSel = "select a.ProjectName, a.CreatorName, b.Amount, b.SignDate, b.ContractorName "
                + " from project a join projectcontract b on a.projectID=b.projectID where a.status=@ProjectStaus and b.status=@ContractStatus order by SignDate desc limit 10";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("ProjectStaus", DbType.String).Value = ProjectStatus.Open.ToString();
            p.Add("ContractStatus", DbType.String).Value = CommonStatus.Effective.ToString();

            IList<IDictionary<string, string>> lst = new List<IDictionary<string, string>>();

            DBHelper.TemplateMySQL.QueryWithRowCallbackDelegate(CommandType.Text, strSel, (dr) =>
            {
                IDictionary<string, string> dic = new Dictionary<string, string>();
                dic.Add("ProjectName", dr.GetStringIfNotNull(0));
                dic.Add("CreatorName", dr.GetStringIfNotNull(1));
                dic.Add("Amount", dr.GetDoubleIfNotNull(2).ToString());
                dic.Add("SignDate", dr.GetDateIfNotNull(3).ToString());
                dic.Add("ContractorName", dr.GetStringIfNotNull(4));

                lst.Add(dic);
            }, p);

            return lst;
        }

        public IList<IDictionary<string, string>> ListAgentSponsorMessage()
        {
            string strSel = "select a.ProjectName, a.CreatorName, b.Amount, a.StartDate, a.EndDate, b.ContractorName, b.AgentID "
                + " from project a join projectcontract b on a.projectID=b.projectID where a.status=@ProjectStaus and b.status=@ContractStatus and b.AgentID is not null order by SignDate desc limit 10";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("ProjectStaus", DbType.String).Value = ProjectStatus.Open.ToString();
            p.Add("ContractStatus", DbType.String).Value = CommonStatus.Effective.ToString();

            IList<IDictionary<string, string>> lst = new List<IDictionary<string, string>>();

            DBHelper.TemplateMySQL.QueryWithRowCallbackDelegate(CommandType.Text, strSel, (dr) =>
            {
                IDictionary<string, string> dic = new Dictionary<string, string>();
                dic.Add("ProjectName", dr.GetStringIfNotNull(0));
                dic.Add("CreatorName", dr.GetStringIfNotNull(1));
                dic.Add("Amount", dr.GetDoubleIfNotNull(2).ToString());
                dic.Add("StartDate", dr.GetDateIfNotNull(3).ToString());
                dic.Add("EndDate", dr.GetDateIfNotNull(4).ToString());
                dic.Add("ContractorName", dr.GetStringIfNotNull(5));
                dic.Add("AgentName", dr.GetIntIfNotNull(6).ToString());

                lst.Add(dic);
            }, p);

            return lst;
        }

        public int GetContractNumber(int projectID, CommonStatus status = CommonStatus.All)
        {
            string strSel = "select count(1) from ProjectContract where ProjectID=@ProjectID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("ProjectID", DbType.Int32).Value = projectID;

            if (status != CommonStatus.All)
            {
                strSel = strSel + " and Status = @Status";
                p.Add("Status", DbType.String).Value = status;
            }

            object obj = DBHelper.TemplateMySQL.ExecuteScalar(CommandType.Text, strSel, p);
            return obj == null ? 0 : obj.ToString().ToInt();
        }

        private ModelList<IDictionary<string, string>> ListContractByStatus(int creatorID, string strSel, string status, int start, int limit)
        {
            ModelList<IDictionary<string, string>> lst = new ModelList<IDictionary<string, string>>();

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            if (creatorID > 0)
                p.Add("CreatorID", DbType.Int32).Value = creatorID;
            if (!strSel.IsNullOrEmpty())
                p.Add("ContractStatus", DbType.String).Value = status;
            if (status == "Effective")
                p.Add("ProjectStatus", DbType.String).Value = ProjectStatus.Open;
            p.Add("start", DbType.Int32).Value = (start - 1) * limit;
            p.Add("limit", DbType.Int32).Value = limit;

            DBHelper.TemplateMySQL.QueryWithRowCallbackDelegate(CommandType.Text, strSel, (dr) =>
            {
                lst.Add(MapEffectiveContract(dr, 0));

                while (dr.Read())
                    lst.Add(MapEffectiveContract(dr, 0));

                bool isNext = dr.NextResult();


                while (dr.Read() && isNext)
                    lst.TotalCount = dr.GetInt32(0);

            }, p);

            return lst;
        }

        #endregion

        #region ProjectData

        private string baseProjectData = "select DataID,ProjectID,ContractID,DataType,DataName,DataValue,UpdateDate,UpdateUser from ProjectData";
        public ProjectData CreateProjectData(ProjectData obj)
        {
            string strIns = @"insert into ProjectData(ProjectID,ContractID,DataType,DataName,DataValue,UpdateDate,UpdateUser) values(@ProjectID,@ContractID,@DataType,@DataName,@DataValue,@UpdateDate,@UpdateUser);SELECT LAST_INSERT_ID() as id";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            p.Add("DataID", DbType.Int32).Value = obj.DataID;
            p.Add("ProjectID", DbType.Int32).Value = obj.ProjectID;
            p.Add("ContractID", DbType.Int32).Value = obj.ContractID;
            p.Add("DataType", DbType.String).Value = obj.DataType;
            p.Add("DataName", DbType.String).Value = obj.DataName;
            p.Add("DataValue", DbType.String).Value = obj.DataValue;
            p.Add("UpdateDate", DbType.DateTime).Value = obj.UpdateDate;
            p.Add("UpdateUser", DbType.Int32).Value = obj.UpdateUser;

            int num = Int32.Parse(DBHelper.TemplateMySQL.ExecuteScalar(CommandType.Text, strIns, p).ToString());
            obj.DataID = num;
            return obj;
        }
        public void UpdateProjectData(ProjectData obj)
        {
            string strUpdate = @"update ProjectData set ProjectID=@ProjectID,ContractID=@ContractID,DataType=@DataType,DataName=@DataName,DataValue=@DataValue,UpdateDate=@UpdateDate,UpdateUser=@UpdateUser where DataID=@DataID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            p.Add("DataID", DbType.Int32).Value = obj.DataID;
            p.Add("ProjectID", DbType.Int32).Value = obj.ProjectID;
            p.Add("ContractID", DbType.Int32).Value = obj.ContractID;
            p.Add("DataType", DbType.String).Value = obj.DataType;
            p.Add("DataName", DbType.String).Value = obj.DataName;
            p.Add("DataValue", DbType.String).Value = obj.DataValue;
            p.Add("UpdateDate", DbType.DateTime).Value = obj.UpdateDate;
            p.Add("UpdateUser", DbType.Int32).Value = obj.UpdateUser;

            DBHelper.TemplateMySQL.ExecuteNonQuery(CommandType.Text, strUpdate, p);
        }
        public ProjectData GetProjectData(int dataid)
        {
            string strSel = baseProjectData + " where DataID=@DataID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("DataID", DbType.Int32).Value = dataid;


            IList<ProjectData> lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<ProjectData>(MapProjectData), p);
            return lst.Count == 0 ? null : lst[0];
        }
        public IList<ProjectData> ListProjectData(int id, ProjectDataType type)
        {
            string strID = type == ProjectDataType.Project ? "ProjectID" : "ContractID";

            string strSel = baseProjectData + " where " + strID + "= @id and DataType=@DataType";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            p.Add("id", DbType.Int32).Value = id;
            p.Add("DataType", DbType.String).Value = type.ToString();

            return DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<ProjectData>(MapProjectData), p);
        }

        public ProjectData GetProjectData(int id, ProjectDataType type, FieldName name)
        {
            string strID = type == ProjectDataType.Project ? "ProjectID" : "ContractID";

            string strSel = baseProjectData + " where " + strID + "= @id and DataType=@DataType and DataName=@DataName";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            p.Add("id", DbType.Int32).Value = id;
            p.Add("DataType", DbType.String).Value = type.ToString();
            p.Add("DataName", DbType.String).Value = name.ToString();

            IList<ProjectData> lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<ProjectData>(MapProjectData), p);

            return lst.Count == 0 ? null : lst[0];

        }

        public ProjectData GetProjectData(int projectID, int contractID, FieldName name)
        {
            string strSel = baseProjectData + " where ProjectID=@ProjectID and ContractID=@ContractID and DataName=@DataName";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            p.Add("ProjectID", DbType.Int32).Value = projectID;
            p.Add("ContractID", DbType.Int32).Value = contractID;
            p.Add("DataName", DbType.String).Value = name.ToString();

            IList<ProjectData> lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<ProjectData>(MapProjectData), p);

            return lst.Count == 0 ? null : lst[0];
        }

        public void DeleteProjectData(int id, ProjectDataType type)
        {
            string strID = type == ProjectDataType.Project ? "ProjectID" : "ContractID";
            string strWhere = type == ProjectDataType.Project ? " and DataType = 'Project'" : " and DataType = 'Contract'";

            string strSel = "delete from ProjectData where " + strID + "= @id" + strWhere;

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            p.Add("id", DbType.Int32).Value = id;

            DBHelper.TemplateMySQL.ExecuteNonQuery(CommandType.Text, strSel, p);
        }

        #endregion

        #region ProjectSearch

        public ProjectSearch CreateProjectSearch(ProjectSearch obj)
        {
            string strIns = @"insert into ProjectSearch(ProjectID,Keywords) values(@ProjectID,@Keywords)";
            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            p.Add("ProjectID", DbType.Int32).Value = obj.ProjectID;
            p.Add("Keywords", DbType.String).Value = obj.KeyWords;

            DBHelper.TemplateMySQL.ExecuteScalar(CommandType.Text, strIns, p);
            return obj;
        }

        public void UpdateProjectSearch(ProjectSearch obj)
        {
            string strUpdate = @"update ProjectSearch set Keywords=@Keywords where ProjectID=@ProjectID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("ProjectID", DbType.Int32).Value = obj.ProjectID;
            p.Add("Keywords", DbType.String).Value = obj.KeyWords;

            DBHelper.TemplateMySQL.ExecuteNonQuery(CommandType.Text, strUpdate, p);
        }

        public void DeleteProjectSearch(string code)
        {
            string strDelete = @"delete from ProjectSearch where ProjectID=@ProjectID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("ProjectID", DbType.Int32).Value = code;

            DBHelper.TemplateMySQL.ExecuteNonQuery(CommandType.Text, strDelete, p);
        }

        #endregion

        #region ProjectCollection

        private string baseProjectCollection = "select CollectionID, UserID,ProjectID,ProjectType,CreateDate from ProjectCollection";

        public void CreateProjectCollection(ProjectCollection obj)
        {
            string strIns = @"insert into ProjectCollection(UserID,ProjectID,ProjectType,CreateDate) values(@UserID,@ProjectID,@ProjectType,@CreateDate)";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("UserID", DbType.Int32).Value = obj.UserID;
            p.Add("ProjectID", DbType.Int32).Value = obj.ProjectID;
            p.Add("ProjectType", DbType.String).Value = obj.ProjectType.ToString();
            p.Add("CreateDate", DbType.DateTime).Value = DateTime.Now;

            DBHelper.TemplateMySQL.ExecuteNonQuery(CommandType.Text, strIns, p);
        }

        public void DeleteProjectCollection(int collectionID)
        {
            string strDel = "delete from ProjectCollection where CollectionID=@CollectionID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("CollectionID", DbType.Int32).Value = collectionID;

            DBHelper.TemplateMySQL.ExecuteNonQuery(CommandType.Text, strDel, p);
        }

        public ProjectCollection GetProjectCollection(int collectionID)
        {
            string strSel = baseProjectCollection + " where CollectionID=@CollectionID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("CollectionID", DbType.Int32).Value = collectionID;

            var lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<ProjectCollection>(MapProjectCollection), p);
            return lst.Count == 0 ? null : lst[0];
        }

        public ProjectCollection GetProjectCollection(int userID, int projectID)
        {
            string strSel = baseProjectCollection + " where UserID=@UserID and ProjectID=@ProjectID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("UserID", DbType.Int32).Value = userID;
            p.Add("ProjectID", DbType.Int32).Value = projectID;

            var lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<ProjectCollection>(MapProjectCollection), p);
            return lst.Count == 0 ? null : lst[0];
        }

        public ModelList<IDictionary<string, string>> ListProjectCollection(int userID, int start, int limit)
        {
            start = start == 0 ? 1 : start;
            limit = limit == 0 ? 10 : limit;

            string strSel = "select sql_calc_found_rows a.ProjectID, a.ProjectName, a.Amount, a.CreateDate, a.Status from Project a join ProjectCollection b on a.ProjectID=b.ProjectID "
             + " where b.UserID=@UserID order by b.CreateDate desc limit @start,@limit;select found_rows()";

            ModelList<IDictionary<string, string>> lst = new ModelList<IDictionary<string, string>>();

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("UserID", DbType.Int32).Value = userID;
            p.Add("start", DbType.Int32).Value = (start - 1) * limit;
            p.Add("limit", DbType.Int32).Value = limit;

            DBHelper.TemplateMySQL.QueryWithRowCallbackDelegate(CommandType.Text, strSel, (dr) =>
            {
                lst.Add(MapMyFavorites(dr, 0));

                while (dr.Read())
                    lst.Add(MapMyFavorites(dr, 0));

                bool isNext = dr.NextResult();

                while (dr.Read() && isNext)
                    lst.TotalCount = dr.GetInt32(0);

            }, p);

            return lst;
        }

        public IList<ProjectCollection> ListProjectCollection(int userID)
        {
            string strSel = baseProjectCollection + " where UserID=@UserID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("UserID", DbType.Int32).Value = userID;

            return DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<ProjectCollection>(MapProjectCollection), p);
        }

        public int CountProjectCollection(int projectID)
        {
            string strSql = "select count(1) from ProjectCollection where ProjectID=@ProjectID";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("ProjectID", DbType.Int32).Value = projectID;

            object obj = DBHelper.TemplateMySQL.ExecuteScalar(CommandType.Text, strSql, p);

            return obj == null ? 0 : obj.ToString().ToInt();
        }

        #endregion

        #region MapRow

        private ProjectData MapProjectData(IDataReader dr, int count)
        {
            ProjectData obj = new ProjectData();
            obj.DataID = dr.GetInt32(0);
            obj.ProjectID = dr.GetInt32(1);
            obj.ContractID = dr.GetInt32(2);
            obj.DataType = (ProjectDataType)Enum.Parse(typeof(ProjectDataType), dr.GetString(3), true);
            obj.DataName = (FieldName)Enum.Parse(typeof(FieldName), dr.GetString(4), true);
            obj.DataValue = dr.IsDBNull(5) ? String.Empty : dr.GetString(5);
            obj.UpdateDate = dr.GetDateTime(6);
            obj.UpdateUser = dr.GetInt32(7);

            return obj;
        }

        private Project MapProject(IDataReader dr, int count)
        {
            Project obj = new Project();
            obj.ProjectID = dr.GetInt32(0);
            obj.ProjectName = dr.IsDBNull(1) ? String.Empty : dr.GetString(1);
            obj.Address = dr.IsDBNull(2) ? String.Empty : dr.GetString(2);
            obj.Amount = dr.GetDouble(3);
            obj.StartDate = dr.GetDateTime(4);
            obj.EndDate = dr.GetDateTime(5);
            obj.Status = (ProjectStatus)Enum.Parse(typeof(ProjectStatus), dr.GetString(6), true);
            obj.Reamrk = dr.IsDBNull(7) ? String.Empty : dr.GetString(7);
            obj.CreatorID = dr.GetInt32(8);
            obj.CreatorType = (APPRole)Enum.Parse(typeof(APPRole), dr.GetString(9), true);
            obj.CreatorName = dr.IsDBNull(10) ? String.Empty : dr.GetString(10);
            obj.CreateDate = dr.GetDateTime(11);
            obj.UpdateDate = dr.GetDateTime(12);
            obj.UpdateUser = dr.GetInt32(13);
            obj.Cover = dr.IsDBNull(14) ? string.Empty : dr.GetString(14);
            obj.CreatorBelongID = dr.IsDBNull(15) ? 0 : dr.GetInt32(15);

            return obj;
        }

        private ProjectContract MapProjectContract(IDataReader dr, int count)
        {
            ProjectContract obj = new ProjectContract();
            obj.ContractID = dr.GetInt32(0);
            obj.ProjectID = dr.GetInt32(1);
            obj.AgentID = dr.IsDBNull(2) ? 0 : dr.GetInt32(2);
            obj.ContractCode = dr.IsDBNull(3) ? String.Empty : dr.GetString(3);
            obj.Status = (CommonStatus)Enum.Parse(typeof(CommonStatus), dr.GetString(4), true);
            obj.Amount = dr.GetDouble(5);
            obj.ContractorID = dr.GetInt32(6);
            obj.ContractorName = dr.IsDBNull(7) ? String.Empty : dr.GetString(7);
            obj.CreateDate = dr.GetDateTime(8);
            obj.SignDate = dr.GetDateTime(9);
            obj.UpdateDate = dr.GetDateTime(10);
            obj.UpdateUser = dr.GetInt32(11);
            obj.SubStatus = (CommonStatus)Enum.Parse(typeof(CommonStatus), dr.GetString(12), true);
            obj.TemplateVersion = dr.IsDBNull(13) ? String.Empty : dr.GetString(13);

            return obj;
        }

        private ProjectCollection MapProjectCollection(IDataReader dr, int count)
        {
            ProjectCollection obj = new ProjectCollection();
            obj.CollectionID = dr.GetInt32(0);
            obj.UserID = dr.GetInt32(1);
            obj.ProjectID = dr.GetInt32(2);
            obj.ProjectType = (APPRole)Enum.Parse(typeof(APPRole), dr.GetString(3), true);
            obj.CreateDate = dr.GetDateTime(4);

            return obj;
        }

        private IDictionary<string, string> MapEffectiveContract(IDataReader dr, int count)
        {
            IDictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("ProjectName", dr.GetString(0));
            dic.Add("ContractID", dr.GetIntIfNotNull(1).DESEncrypt());
            dic.Add("SignDate", dr.GetDateIfNotNull(2).ToString(DataFormatter.ShortDate));
            dic.Add("Amount", dr.GetDoubleIfNotNull(3).ToString());
            dic.Add("ContractorName", dr.GetStringIfNotNull(4));
            dic.Add("SponsorAmount", dr.GetStringIfNotNull(5));
            dic.Add("CreateDate", dr.GetDateIfNotNull(6).ToString(DataFormatter.ShortDate));
            dic.Add("Status", dr.GetStringIfNotNull(7));
            dic.Add("ProjectID", dr.GetIntIfNotNull(8).DESEncrypt());
            dic.Add("CreatorName", dr.GetStringIfNotNull(9));
            dic.Add("CreatorID", dr.GetIntIfNotNull(10).ToString());

            return dic;
        }

        private IDictionary<string, string> MapMyFavorites(IDataReader dr, int count)
        {
            IDictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("ProjectID", dr.GetIntIfNotNull(0).DESEncrypt());
            dic.Add("ProjectName", dr.GetStringIfNotNull(1));
            dic.Add("Amount", dr.GetDoubleIfNotNull(2).ToString());
            dic.Add("CreateDate", dr.GetDateIfNotNull(3).ToString(DataFormatter.ShortDate));
            dic.Add("Status", dr.GetStringIfNotNull(4));

            return dic;
        }

        #endregion
    }
}
